/*
 * CMichatDb.cpp
 *
 *  Created on: 2011-2-22
 *  Author: wanghuan
 *  
 *  单例模式的CMichatDbManager在创建单例对象时创建一个名为michat.db的数据库文件（如果不存在的话）
 *  
 *  1.获得句柄方法：
 *      
 *      CMichatDbManager * mgr;
        TRAPD(ret, (mgr = CMichatDbManager::GetInstanceL()));
        if (ret != KErrNone)
        {
            LOG(ERROR)<<"Get CMichatDbManager Failed";
            return;
        }
        
    2.创建数据库表：
                     
                      每个数据库表由一个TableDesC对象描述，构造参数为数据库表名，通过AddColumn方法按顺序增加一列 
                     后续的数据库操作函数都需要对应的TableDesC对象的引用 
                     
        TableDesC td("test");
        td.AddColumn("id", EDbColInt32, TDbCol::EAutoIncrement);
        td.AddColumn("name", EDbColText8);
        TRAPD(ret, (mgr->CreateTableL(td)));
    
    3.插入一条记录：    
    
                    每条记录由TableItem描述，通过SetXXX方法为某列赋值，注意列索引从1开始计数
        TableItem ti;
        ti.SetString8(2, YString8("xing"));
        TRAPD(ret, (mgr->InsertItemL(td, ti)));
    
    4. 更新记录：    
        
                     第二个参数为where条件语句，当where为空时更新所有记录
        TableItem it;
        it.SetString8(2, YString8("huan"));
        TRAPD(ret, (mgr->UpdateItemL(td, it, YString())));
    
    5. 查询记录
        
                     第二个参数为where条件语句，请注意symbian dbms仅支持SQL标准的一个子集，不支持级联查询
        
        see http://library.forum.nokia.com/topic/S60_3rd_Edition_Cpp_Developers_Library/GUID-35228542-8C95-4849-A73F-2B4F082F0C44/html/SDL_93/doc_source/guide/
        System-Libraries-subsystem-guide/dbms/DBMSRowsets/DBMSSQL/SupportedSQLSubset.guide.html#DBMSRowsetsGuide%2eSQL%2esupported%2dSQL%2dsubset
        
        TRAPD(ret, (mgr->QueryItemL(td, YString("SELECT * from test"))));
        ResultSet &rs = mgr->GetResultSet();
        
    6. 删除记录    
    
                     第二个参数为where条件语句，当where为空时删除所有记录
        TRAPD(ret, mgr->DeleteItemL(td, YString()));
 */

#include "CMichatDb.h"
#include "ylog.h"
#include "yfilesys.h"
#include <e32def.h>
#include <bautils.h>

_LIT(KtxDatabaseName, "michat.db");

CMichatDbManager * CMichatDbManager::instance = NULL;

CMichatDbManager::~CMichatDbManager()
{
    TRACK(FATAL);
    iMichatDatabase.Close();
    iFsSession.Close();
    instance = NULL;
}

CMichatDbManager * CMichatDbManager::GetInstanceLC()
{
    TRACK(FATAL);
    if (instance == NULL)
    {
        instance = new (ELeave) CMichatDbManager();
        CleanupStack::PushL(instance);
        instance->InitDatabaseL();
    }else{
        CleanupStack::PushL(instance);
    }
    return instance;
}

CMichatDbManager * CMichatDbManager::GetInstanceL()
{
    TRACK(FATAL);
    CMichatDbManager * mgr = CMichatDbManager::GetInstanceLC();
    CleanupStack::Pop(mgr);
    return mgr;
}

void CMichatDbManager::InitDatabaseL()
{
    TRACK(FATAL);
    User::LeaveIfError(iFsSession.Connect());
    YString DBFileName("C:\\data\\miliao\\test.dbms");//YFileSys::getPrivatePath().ToUtf16();
    //DBFileName.Append(KtxDatabaseName);
    
    if (BaflUtils::FileExists(iFsSession, DBFileName.ToDesC()))
    {
        User::LeaveIfError(iMichatDatabase.Open(iFsSession, DBFileName.ToDesC()));
    }else
    {
        User::LeaveIfError(iMichatDatabase.Create(iFsSession, DBFileName.ToDesC()));
    }
}

void CMichatDbManager::CreateTableL(const TableDesC &td)
{
    TRACK(FATAL);
    CDbColSet * columns = CDbColSet::NewLC();
    
    for (TableDesC::column_iterator it = td.begin(); it != td.end(); ++it)
    {
        const TableColumn &tc = *it;
        TDbCol col(tc.iColName.ToDesC(), tc.iColType);
        col.iAttributes = tc.iAttributes;
        columns->AddL(col);
    }
    
    User::LeaveIfError(iMichatDatabase.CreateTable(td.GetName().ToDesC(), *columns));
    CleanupStack::PopAndDestroy(columns);
}
RDbNamedDatabase& CMichatDbManager::GetDatabase()
{
   return iMichatDatabase;
}
// you should not call any of functions after this
void CMichatDbManager::Destroy()
{
   iMichatDatabase.Destroy(); 
}

void CMichatDbManager::SetViewValue(TDbColType type, RDbView &view, TInt idx, const ColumnValue &cv)
{
    TRACK(FATAL);
    switch(type)
    {
    case EDbColBit:
    case EDbColInt8:
    case EDbColInt16:
    case EDbColInt32:
        view.SetColL(idx, cv.val.sint);
        break;
    case EDbColUint8:
    case EDbColUint16:
    case EDbColUint32:
        view.SetColL(idx, cv.val.uint);
        break;
    case EDbColInt64:
        view.SetColL(idx, cv.val.int64);
        break;
    case EDbColReal32:
        view.SetColL(idx, cv.val.real32);
        break;
    case EDbColReal64:
        view.SetColL(idx, cv.val.real64);
        break;
    case EDbColDateTime:
        view.SetColL(idx, cv.time);
        break;
    case EDbColText8:
        view.SetColL(idx, cv.str8.ToDesC());
        break;
    case EDbColText16:
        view.SetColL(idx, cv.str16.ToDesC());
        break;
    case EDbColLongText8:
    case EDbColLongText16:
        WriteLongText(type, view, idx, cv);
        break;
    default:
        break;
    }
}

void CMichatDbManager::GetColumnValue(TDbColType type, ColumnValue &cv, RDbView &view, TInt idx)
{
    TRACK(FATAL);
    switch(type)
    {
    case EDbColBit:
    case EDbColInt8:
        cv.val.sint = view.ColInt8(idx);
        break;
    case EDbColInt16:
        cv.val.sint = view.ColInt16(idx);
        break;
    case EDbColInt32:
        cv.val.int32 = view.ColInt32(idx);
        break;
    case EDbColInt64:
        cv.val.int64 = view.ColInt64(idx);
        break;
    case EDbColUint8:
        cv.val.uint = view.ColUint8(idx);
        break;
    case EDbColUint16:
        cv.val.uint = view.ColUint16(idx);
        break;
    case EDbColUint32:
        cv.val.uint = view.ColUint32(idx);
        break;
    case EDbColReal32:
        cv.val.real32 = view.ColReal32(idx);
        break;
    case EDbColReal64:
        cv.val.real64 = view.ColReal64(idx);
        break;
    case EDbColDateTime:
        cv.time = view.ColTime(idx);
        break;
    case EDbColText8:
        cv.str8 = view.ColDes8(idx);
        break;
    case EDbColText16:
        cv.str16 = view.ColDes16(idx);
        break;
    case EDbColLongText8:
    case EDbColLongText16:
        ReadLongText(type, cv, view, idx);
        break;
    default:
        break;
    }
} 

void CMichatDbManager::WriteLongText(TDbColType type, RDbView &view, TInt idx, const ColumnValue &cv)
{
    TRACK(FATAL);
    char buf[10];
    YString8 tmp8;
    YString tmp;
    
//    if (type == EDbColLongText8)
//    {
//        sprintf(buf, "%-10d", cv.str8.GetLength());
//        tmp8.Append(buf);
//        tmp8.Append(cv.str8);
//        view.SetColL(idx, tmp8.ToDesC());
//    }
//    else
//    {
//        sprintf(buf, "%-10d", cv.str16.GetLength());
//        tmp.Append(buf);
//        tmp.Append(cv.str16);
//        view.SetColL(idx, tmp.ToDesC());
//    }
    RDbColWriteStream ws;
    ws.OpenLC(view, idx);
    if (type == EDbColLongText8)
        ws.WriteL(cv.str8.ToDesC());
    else
        ws.WriteL(cv.str16.ToDesC());
    ws.Close();
    CleanupStack::Pop();
}

void CMichatDbManager::ReadLongText(TDbColType type, ColumnValue &cv, RDbView &view, TInt idx)
{
    TRACK(FATAL);
    HBufC8 * heapBuf8;
    HBufC * heapBuf;
    TInt len;
        
    RDbColReadStream rs;
    rs.OpenL(view, idx);
    len = view.ColLength(idx);
    if (type == EDbColLongText8)
    {
        heapBuf8 = HBufC8::NewLC(len);
        TPtr8 ptr(heapBuf8->Des());
        rs.ReadL(ptr, len);
        cv.str8 = heapBuf8->Des();
    }
    else
    {
        heapBuf = HBufC::NewLC(len);
        TPtr ptr(heapBuf->Des());
        rs.ReadL(ptr, len);
        cv.str16 = heapBuf->Des();
    }
    
    CleanupStack::PopAndDestroy();
    rs.Close();
}

void CMichatDbManager::SetColumnValue(TDbColType type, ColumnValue &cv, const TAny *data)
{
    TRACK(FATAL);
    switch (type)
    {
    case EDbColBit:
    case EDbColInt8:
    case EDbColInt16:
        cv.val.sint = *(TInt *)data;
        break;
    case EDbColInt32:
        cv.val.int32 = *(TInt32 *)data;
        break;
    case EDbColInt64:
        cv.val.int64 = *(TInt64 *)data;
        break;
    case EDbColUint8:
    case EDbColUint16:
        cv.val.uint = *(TUint *)data;
        break;
    case EDbColUint32:
        cv.val.uint32 = *(TUint32 *)data;
        break;
    case EDbColReal32:
        cv.val.real32 = *(TReal32 *)data;
        break;
    case EDbColReal64:
        cv.val.real64 = *(TReal64 *)data;
        break;
    case EDbColDateTime:
        cv.time = *(TTime *)data;
        break;
    case EDbColText8:
    case EDbColLongText8:
        cv.str8 = *(YString8 *)data;
        break;
    case EDbColText16:
    case EDbColLongText16:
        cv.str16 = *(YString *)data;
        break;
    default:
        break;
    }
}
void CMichatDbManager::InsertBatchItemsL(const TableDesC &td, const std::vector<TableItem>& tis)
{
    TRACK(FATAL);
   TFileName QueryBuffer;
    QueryBuffer.Copy(_L("SELECT * FROM "));
    QueryBuffer.Append(td.GetName().ToDesC());

    iMichatDatabase.Begin();
    RDbView view;
    view.Prepare(iMichatDatabase, TDbQuery(QueryBuffer), RDbView::EInsertOnly);
    CleanupClosePushL(view);
    size_t size = tis.size();

    for (int i = 0; i < size; i++) {
        view.InsertL();
        for (TableItem::column_iterator it = tis[i].begin(); it != tis[i].end(); ++it) {
            const ColumnValue &cv = it->second;
            TDbColType type = td.GetColType(it->first - 1); //vector从0开始计数
            SetViewValue(type, view, it->first, cv);
        }
        view.PutL();
    }
   
    view.Close();

    CleanupStack::PopAndDestroy(1);
    iMichatDatabase.Commit();
    iMichatDatabase.Compact();
}
void CMichatDbManager::InsertItemL(const TableDesC &td, const TableItem &ti)
{
    TRACK(FATAL);
    TFileName QueryBuffer;
    QueryBuffer.Copy(_L("SELECT * FROM "));
    QueryBuffer.Append(td.GetName().ToDesC());
    
    RDbView view;
    view.Prepare(iMichatDatabase, TDbQuery(QueryBuffer), RDbView::EInsertOnly);
    CleanupClosePushL(view);
    
    view.InsertL();
    for (TableItem::column_iterator it = ti.begin(); it != ti.end(); ++it)
    {
        const ColumnValue &cv = it->second;
        TDbColType type = td.GetColType(it->first - 1); //vector从0开始计数
        SetViewValue(type, view, it->first, cv);
    }
    view.PutL();
    view.Close();
    
    CleanupStack::PopAndDestroy(1);
    iMichatDatabase.Compact();
}

void CMichatDbManager::UpdateItemL(const TableDesC &td, const TableItem &ti, const YString &where)
{
    TRACK(FATAL);
    TFileName QueryBuffer;
    QueryBuffer.Copy(_L("SELECT * FROM "));
    QueryBuffer.Append(td.GetName().ToDesC());
    if (!where.IsEmpty())
    {
        QueryBuffer.Append(_L(" WHERE "));
        QueryBuffer.Append(where.ToDesC());
    }
   
    RDbView view;
    CleanupClosePushL(view);
    view.Prepare(iMichatDatabase, TDbQuery(QueryBuffer));
    view.FirstL();
    
    while(view.AtRow())
    {
        view.UpdateL();
        for (TableItem::column_iterator it = ti.begin(); it != ti.end(); ++it)
        {
            const ColumnValue &cv = it->second;
            TDbColType type = td.GetColType(it->first - 1);
            SetViewValue(type, view, it->first, cv);
        }
        view.PutL();
        view.NextL();
    }
    
    view.Close();
    CleanupStack::PopAndDestroy(1);
    iMichatDatabase.Compact();
}
void CMichatDbManager::UpdateOneColumnL(const YString& tableName, const YString &columnName,
    const YString& where, const ColumnValue &value, TDbColType type)
{
    TRACK(FATAL);
    TFileName QueryBuffer;
    QueryBuffer.Copy(_L("SELECT "));
    QueryBuffer.Append(columnName.ToDesC());
    QueryBuffer.Append(_L(" FROM "));
    QueryBuffer.Append(tableName.ToDesC());
    if (!where.IsEmpty()) {
        QueryBuffer.Append(_L(" WHERE "));
        QueryBuffer.Append(where.ToDesC());
    }

    iMichatDatabase.Begin();
    RDbView view;
    CleanupClosePushL(view);
    view.Prepare(iMichatDatabase, TDbQuery(QueryBuffer));
    view.FirstL();

    while (view.AtRow()) {
        view.UpdateL();
        SetViewValue(type, view, 1, value);
        view.PutL();
        view.NextL();
    }

    view.Close();
    CleanupStack::PopAndDestroy(1);
    iMichatDatabase.Commit();
}

void CMichatDbManager::DeleteItemL(const TableDesC &td, const YString &where, const YString &order, int limit)
{
    TRACK(FATAL);
    TFileName QueryBuffer;
    QueryBuffer.Copy(_L("SELECT * FROM "));
    QueryBuffer.Append(td.GetName().ToDesC());
    if (!where.IsEmpty()) 
    {
        QueryBuffer.Append(_L(" WHERE "));
        QueryBuffer.Append(where.ToDesC());
    }
    if (!order.IsEmpty())
    {
        QueryBuffer.Append(_L(" ORDER BY "));
        QueryBuffer.Append(order.ToDesC());
    }
    
    RDbView view;
    view.Prepare(iMichatDatabase, TDbQuery(QueryBuffer));
    CleanupClosePushL(view);
   // User::LeaveIfError(view.EvaluateAll());
    view.FirstL();
    
    while(view.AtRow() && limit--)
    {
        view.GetL();
        view.DeleteL();
        view.NextL();
    }
    
    view.Close();
    CleanupStack::PopAndDestroy(1);
    iMichatDatabase.Compact();
}

int CMichatDbManager::GetRecordCount(TableDesC &td)
{
    YString sql("SELECT * FROM ");
    sql.Append(td.GetName());
    RDbView view;
    int count;
    
    view.Prepare(iMichatDatabase, TDbQuery(sql.ToDesC()), view.EReadOnly);
    CleanupClosePushL(view);
    User::LeaveIfError(view.EvaluateAll());
    count = view.CountL();
    CleanupStack::PopAndDestroy();
    return count;
}

void CMichatDbManager::BeginTransaction()
	{
		iMichatDatabase.Begin();
	}

void CMichatDbManager::EndTransaction()
	{
		iMichatDatabase.Commit();
	}

int CMichatDbManager::QueryItemL(const TableDesC &/*td*/, const YString &sql, int limit,int startPosition)
{
    RDbView view;
    int count = 0;
    view.Prepare(iMichatDatabase, TDbQuery(sql.ToDesC()), view.EReadOnly);
    CleanupClosePushL(view);
    User::LeaveIfError(view.EvaluateAll());
    
    view.FirstL();
    
    while(view.AtRow()&&startPosition--)
    {
        view.NextL();
    }
    rs.clear();
    
    while(view.AtRow() && limit--)
    {
        ResultItem ri;
        view.GetL();
        CDbColSet * pset = view.ColSetL();
        for (int i = 1; i <= pset->Count(); i++) //CDbColSet从1开始计数
        {
            TDbColType type = (*pset)[i].iType;
            ColumnValue cv;
            GetColumnValue(type, cv, view, i);
            ri.AddColumnValue(i, cv);
        }
        delete pset;
        rs.push_back(ri);
        view.NextL();
        count++;
    }
    
    view.Close();
    CleanupStack::PopAndDestroy();
    return count; 
}
void CMichatDbManager::Compact()
{
    iMichatDatabase.Compact();
}

bool CMichatDbManager::IsExists(const TableDesC &td, const YString &where)
{
    RDbView view;
    YString sql("SELECT * FROM ");
    sql.Append(td.GetName());
    if (!where.IsEmpty())
    {
        sql.Append(" WHERE ");
        sql.Append(where);
    }
    view.Prepare(iMichatDatabase, TDbQuery(sql.ToDesC()), view.EReadOnly);
    CleanupClosePushL(view);
  //  User::LeaveIfError(view.EvaluateAll());
    bool exists = view.CountL() > 0;
    CleanupStack::PopAndDestroy();
    return exists;
}

void TableItem::SetInt(TInt aIdx, TInt aVal)
{
    TRACK(FATAL);
    ColumnValue cv;
    cv.val.sint = aVal;
    this->AddColumnValue(aIdx, cv);
}

void TableItem::SetInt32(TInt aIdx, TInt32 aVal)
{
    TRACK(FATAL);
    ColumnValue cv;
    cv.val.int32 = aVal;
    this->AddColumnValue(aIdx, cv);
}

void TableItem::SetInt64(TInt aIdx, TInt64 aVal)
{
    TRACK(FATAL);
    ColumnValue cv;
    cv.val.int64 = aVal;
    this->AddColumnValue(aIdx, cv);
}

void TableItem::SetUint(TInt aIdx, TUint aVal)
{
    TRACK(FATAL);
    ColumnValue cv;
    cv.val.uint = aVal;
    this->AddColumnValue(aIdx, cv);
}

void TableItem::SetUint32(TInt aIdx, TUint32 aVal)
{
    TRACK(FATAL);
    ColumnValue cv;
    cv.val.uint32 = aVal;
    this->AddColumnValue(aIdx, cv);
}

void TableItem::SetReal32(TInt aIdx, TReal32 aVal)
{
    TRACK(FATAL);
    ColumnValue cv;
    cv.val.real32 = aVal;
    this->AddColumnValue(aIdx, cv);
}

void TableItem::SetReal64(TInt aIdx, TReal64 aVal)
{
    TRACK(FATAL);
    ColumnValue cv;
    cv.val.real64 = aVal;
    this->AddColumnValue(aIdx, cv);
}

void TableItem::SetTime(TInt aIdx, const TTime &aVal)
{
    TRACK(FATAL);
    ColumnValue cv;
    cv.time = aVal;
    this->AddColumnValue(aIdx, cv);
}

void TableItem::SetString8(TInt aIdx, const YString8 &aVal)
{
    TRACK(FATAL);
    ColumnValue cv;
    cv.str8 = aVal;
    this->AddColumnValue(aIdx, cv);
}

void TableItem::SetString16(TInt aIdx, const YString &aVal)
{
    TRACK(FATAL);
    ColumnValue cv;
    cv.str16 = aVal;
    this->AddColumnValue(aIdx, cv);
}
